package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.model.Users;
import com.util.DBUtil;

public class UsersDAO implements IUsersDAO{
	Connection con = new DBUtil().getConnection();

	@Override
	public boolean insert(Users obj) {
		boolean flag=false;
		PreparedStatement st = null;
		String sql = "insert into Users (username,password,role_id) values(?,?,?)";
		try {
			st = con.prepareStatement(sql);
			st.setString(1, obj.getUsername());
			st.setString(2, obj.getPassword());
			st.setInt(3,obj.getRoleId());
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	@Override
	public boolean update(Users obj){
		boolean flag=false;
		String sql="update Users set username=?,password=?,role_id=? where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, obj.getUsername());
			st.setString(2, obj.getPassword());
			st.setInt(3, obj.getRoleId());
			st.setInt(4, obj.getId());
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	@Override
	public boolean delete(Integer id){
		boolean flag=false;
		String sql="delete from Users where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}

	@Override
	public Users get(Integer id){
		Users obj=new Users();
		String sql="select * from Users where id=?";
		PreparedStatement st = null;
		ResultSet rs=null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs=st.executeQuery();
			if(rs.next()){
				obj.setId(rs.getInt("id"));
				obj.setUsername(rs.getString("username"));
				obj.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return obj;
	}
	@Override
	public List<Users> getAll(){
		return findSql("select * from Users");
	}
	
	@Override
	public List<Users> findSql(String sql) {
		Users obj=null;
		List<Users> list=new ArrayList<Users>();
		Statement st = null;
		ResultSet rs=null;
		try {
			st = con.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next()){
				obj=new Users();
				obj.setId(rs.getInt("id"));
				obj.setUsername(rs.getString("username"));
				obj.setPassword(rs.getString("password"));
				obj.setRoleId(rs.getInt("role_id"));
				list.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	public boolean insert(String name,String psw) {
		boolean flag=false;
		PreparedStatement st = null;
		String sql = "insert into Users (username,password,role_id) values(?,?,?)";
		try {
			st = con.prepareStatement(sql);
			st.setString(1, name);
			st.setString(2, psw);
			st.setInt(3, 2);
			int n = st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public Users get(String name,String psw){
		Users obj=new Users();
		String sql="select * from Users where username=? and password=?";
		PreparedStatement st = null;
		ResultSet rs=null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, name);
			st.setString(2, psw);
			rs=st.executeQuery();
			if(rs.next()){
				obj.setId(rs.getInt("id"));
				obj.setUsername(rs.getString("username"));
				obj.setPassword(rs.getString("password"));
				obj.setRoleId(rs.getInt("role_id"));
			}else obj=null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return obj;
	}
	public String get(String name){
		String obj="null";
		String sql="select * from Users where username=?";
		PreparedStatement st = null;
		ResultSet rs=null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, name);
			rs=st.executeQuery();
			if(rs.next()){
				obj=rs.getString("username");
			}else obj="null";
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return obj;
	}
}
